package com.bo.controller;

import cn.hutool.core.date.DateUtil;
import com.bo.db.DataSourceMDBConfig;
import com.bo.enity.*;
import com.bo.result.R;
import com.bo.result.Result;
import com.bo.result.ResultCodeEnum;
import com.bo.utils.BigDecimalUtils;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

@RestController
@RequestMapping("/shajiang")
@Slf4j
public class SJController {

    @Value("${mdb.path}")
    private String BASE_PATH;

    @RequestMapping("/result")
    public R getResult(){
        ResultCodeEnum resultCodeEnum = ResultCodeEnum.SUCCESS;
        return R.ok();
    }

    @RequestMapping("/exception")
    public R getException(){
        throw new NullPointerException();
    }

    @Resource
    private DataSourceMDBConfig access;

    /**
     * 中间件任务，当新增调度时，将中控数据通过该接口写入到access数据库中
     * @param baseSchedulingDetails
     * @return
     */
    @ApiOperation(value = "生产调度明细-中间件任务", notes = "生产调度明细-中间件任务")
    @PostMapping(value = "/schedulingTask")	//中间件任务
    public R schedulingTask(@Validated @RequestBody BaseSchedulingDetails baseSchedulingDetails) throws SQLException {
        //先将要写到access数据库的字段存到add表中
        //D:/work/Access/Net_Duilie.mdb
        log.info("生产调度明细-中间件任务" + baseSchedulingDetails.toString());

        //任务单数据 先查询是否存在该任务单，如果不存在，则新增
        try(Connection taskConn = access.getAccessConnection(BASE_PATH+"Task.mdb", "", "")){
            String sql = "select * from 生产记录 where 任务单号 = '" + baseSchedulingDetails.getOrderNo() + "'" ;
            Statement taskSta = taskConn.createStatement();
            ResultSet result = taskSta.executeQuery(sql);
            if (result == null || !result.next()){
                //不存在该任务单，新增
                PreparedStatement insert = taskConn.prepareStatement("insert into 生产记录(任务单号,配方编号,客户名称,工程名称,施工部位," +
                    "浇注方式,车次,供应量,施工地点,起运地点,砼标号,合同编号,塌落度,联系电话,日期,排单车次,排单供应量,需方量,石子粒径,备注,执行状态) " +
                        " values(?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?)");
                insert.setString(1, baseSchedulingDetails.getOrderNo());
                insert.setString(2, baseSchedulingDetails.getRatioNo());
                insert.setString(3, baseSchedulingDetails.getConstructionUnit()); //客户名称
                insert.setString(4, baseSchedulingDetails.getEngineeringName());//工程名称
                insert.setString(5, baseSchedulingDetails.getStructuralParts());//施工部位
                insert.setString(6, baseSchedulingDetails.getIsPump());//浇注方式
                insert.setString(7, "1"); //车次
                insert.setInt(8, BigDecimalUtils.subtract(baseSchedulingDetails.getSupplyQuantity(), baseSchedulingDetails.getLeftover()).intValue());
                insert.setString(9, baseSchedulingDetails.getEngineeringAddress()); //施工地点
                insert.setString(10, "");
                insert.setString(11, baseSchedulingDetails.getProductName()); //砼标号
                insert.setString(12, baseSchedulingDetails.getContractCode());
                insert.setString(13, baseSchedulingDetails.getSlumps()); //塌落度
                insert.setString(14, "");
                insert.setString(15, DateUtil.format(baseSchedulingDetails.getCreateTime(), "yyyy-MM-dd")); //日期
                insert.setString(16, "1");// 排单车次
                insert.setInt(17, baseSchedulingDetails.getProductionQuantity().intValue());
                insert.setInt(18, baseSchedulingDetails.getSupplyQuantity().intValue());
                insert.setString(19, baseSchedulingDetails.getProductName());
                insert.setString(20, baseSchedulingDetails.getRemark());
                insert.setString(21, "执行");
                insert.executeUpdate();
                insert.close();
            }else {
                result.close();
            }
            taskSta.close();
        }catch (Exception e){
            log.error("天造砂浆-创建任务单异常", e);
            return R.error("天造砂浆-创建任务单异常" + e.getMessage());
        }

        //调度数据
        try(Connection DuilieConn = access.getAccessConnection(BASE_PATH+"Net_Duilie.mdb", "", "")){
            PreparedStatement duilieSta =
                    DuilieConn.prepareStatement("insert into DL(任务单号, 车号, 驾驶员, 砂浆量, 砼方量, 客户名称,工程名称,施工部位,配比号," +
                            "车次,供应量,砼标号,机组编号,排单车次,排单供应量,浇注方式,施工地点,时间,合同编号,塌落度,联系电话,需方量,石子粒径,备注,公司代号,厂别,流水号,打印方量) " +
                            " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            duilieSta.setString(1, baseSchedulingDetails.getOrderNo());
            duilieSta.setString(2, baseSchedulingDetails.getWagon());
            duilieSta.setString(3, baseSchedulingDetails.getDriver());
            duilieSta.setInt(4, baseSchedulingDetails.getProductionQuantity().intValue());
            duilieSta.setString(5, "0");//砼方量
            //tatement.setInt(6,baseSchedulingDetails.getSignedQuantity()); 签收量
            duilieSta.setString(6, baseSchedulingDetails.getConstructionUnit());//客户名称
            duilieSta.setString(7, baseSchedulingDetails.getEngineeringName());//工程名称
            duilieSta.setString(8, baseSchedulingDetails.getStructuralParts());//施工部位
            duilieSta.setString(9, baseSchedulingDetails.getRatioNo());//配比号
            duilieSta.setInt(10, baseSchedulingDetails.getTrainNumber());//车次
            duilieSta.setInt(11, baseSchedulingDetails.getSupplyQuantity().intValue());//供应量
            duilieSta.setString(12, baseSchedulingDetails.getProductName());//砼标号
            duilieSta.setString(13, "1");//机组编号
            duilieSta.setString(14, "1");//排单车次
            duilieSta.setInt(15, baseSchedulingDetails.getSupplyQuantity().intValue());//排单供应量
            duilieSta.setString(16, "1");//浇注方式
            duilieSta.setString(17, baseSchedulingDetails.getEngineeringAddress());//施工地点
            duilieSta.setString(18, DateUtil.format(baseSchedulingDetails.getCreateTime(), "MM/dd/yyyy HH:mm:ss" ));//时间
            duilieSta.setString(19, baseSchedulingDetails.getContractCode());//合同编号
            duilieSta.setString(20, baseSchedulingDetails.getSlumps());//塌落度
            duilieSta.setString(21, "1");//联系电话
            duilieSta.setInt(22, baseSchedulingDetails.getSupplyQuantity().intValue());//需方量
            duilieSta.setString(23, baseSchedulingDetails.getProductName());//石子粒径
            duilieSta.setString(24, baseSchedulingDetails.getRemark());//备注
            duilieSta.setInt(25, baseSchedulingDetails.getTenantId());//公司代号
            duilieSta.setString(26, baseSchedulingDetails.getCompany());//厂别
            duilieSta.setString(27, baseSchedulingDetails.getTransportNo());//流水号
            duilieSta.setString(28, "0");//打印方量
            duilieSta.executeUpdate();
            duilieSta.close();
        }catch (Exception e){
            log.error("天造砂浆-创建调度运输单异常", e);
            return R.error("天造砂浆-创建调度运输单异常" + e.getMessage());
        }

        return R.ok();
    }

    /**
     * 砂浆中间件任务，当新增配比时，将标准配比数据通过该接口写入到access数据库中
     * @param baseRatioManagementVOJson
     * @throws SQLException
     * @return
     */
    @ApiOperation(value = "标准配比-中间件任务", notes = "标准配比-中间件任务")
    @PostMapping(value = "/ratioTask")	//中间件任务
    public R mortarRatioTask(@Validated @RequestBody BaseRatioManagementVO baseRatioManagementVOJson) throws SQLException {

        log.info("标准配比-中间件任务" + baseRatioManagementVOJson.toString());

        List<BaseRatioManagementDetail> baseRatioManagementDetailList = baseRatioManagementVOJson.getBaseRatioManagementDetailList();
        try(Connection connection = access.getAccessConnection(BASE_PATH+"peibi.mdb", "", "")){

            Map<Integer, BigDecimal> numberMap = baseRatioManagementDetailList.stream().
                    filter(detail -> detail.getSort() != null).
                    collect(Collectors.toMap(BaseRatioManagementDetail::getSort,
                            detail -> detail.getConstNum() == null? BigDecimal.ZERO : detail.getConstNum(),
                            (a, b) -> b));
            //先查询是否存在该配比，如果存在，则更新，如果不存在，则新增
            String sql = "select * from PB where pbh = '" + baseRatioManagementVOJson.getRatioCode() + "'" ;
            Statement statement = connection.createStatement();
            ResultSet result = statement.executeQuery(sql);
            if (result == null || !result.next()){
                //不存在该配比，新增
                PreparedStatement insert =
                        connection.prepareStatement("insert into PB(pbh,tbh,[Material(1)],[Material(2)],[Material(3)],[Material(7)],[Material(8)]," +
                                "[Material(11)],[Material(15)],[Material(19)],[Material(20)]) values(?,?,?,?,?,?,?,?,?,?,?)");
                insert.setString(1, baseRatioManagementVOJson.getRatioCode());
                insert.setString(2, baseRatioManagementVOJson.getProductName()+baseRatioManagementVOJson.getMortarTank());
                insert.setString(3, numberMap.get(1).toString());
                insert.setString(4, numberMap.get(2).toString());
                insert.setString(5, numberMap.get(3).toString());
                insert.setString(6, numberMap.get(4).toString());
                insert.setString(7, numberMap.get(5).toString());
                insert.setString(8, numberMap.get(6).toString());
                insert.setString(9, numberMap.get(7).toString());
                insert.setString(10, numberMap.get(8).toString());
                insert.setString(11, numberMap.get(9).toString());
                insert.executeUpdate();
                insert.close();
            }else {
                //存在该配比，更新
                PreparedStatement update =
                        connection.prepareStatement("update PB set [Material(1)]=?,[Material(2)]=?,[Material(3)]=?,[Material(7)]=?,[Material(8)]=?,[Material(11)]=?,[Material(15)]=?,[Material(19)]=?,[Material(20)]=? where pbh =?");
                update.setString(1, numberMap.get(1).toString());
                update.setString(2, numberMap.get(2).toString());
                update.setString(3, numberMap.get(3).toString());
                update.setString(4, numberMap.get(4).toString());
                update.setString(5, numberMap.get(5).toString());
                update.setString(6, numberMap.get(6).toString());
                update.setString(7, numberMap.get(7).toString());
                update.setString(8, numberMap.get(8).toString());
                update.setString(9, numberMap.get(9).toString());
                update.setString(10, baseRatioManagementVOJson.getRatioCode());
                update.executeUpdate();
                update.close();
                result.close();
            }
            statement.close();
        }catch (Exception e){
            log.error("天造砂浆-创建标准配比-任务异常", e);
            return R.error("天造砂浆-创建标准配比-任务异常" + e.getMessage());
        }

        return R.ok();
    }

    /**
     * Access查询
     *
     * @throws Exception 异常
     *
     */
    @ApiOperation(value = "生产任务查询", notes = "生产任务查询")
    @GetMapping(value = "/productionTask")	//中间件任务
    public Result<Map<String, List<BaseProductionTask>>> productionTask(@RequestParam(name="id",required=true) Integer id) throws Exception {

        log.info("天造砂浆-生产任务查询" + id.toString());
        try(Connection connection = access.getAccessConnection(BASE_PATH+"scjlaccess.mdb", "", "");){
            Result<Map<String, List<BaseProductionTask>>> result = select(connection,id);
            Map<String, List<BaseProductionTask>> result1 = result.getResult();
            log.info(result1.toString());
            return Result.ok(result1);
        }catch (Exception e){
            log.error("天造砂浆-生产任务查询异常", e);
        }
        return Result.ok();
    }

    /**
     * 手动根据生产任务单调用查询生产任务数据
     *
     * @throws Exception 异常
     *
     */
    @ApiOperation(value = "手动根据生产任务单调用查询生产任务数据", notes = "手动根据生产任务单调用查询生产任务数据")
    @GetMapping(value = "/productionTaskByTaskNo")	//中间件任务
    public Result<Map<String, List<BaseProductionTask>>> productionTaskByTaskNo(@RequestParam(name="taskNoList",required=true) String taskNoList) throws Exception {

        log.info("天造砂浆-生产任务查询 By TaskNo" + taskNoList.toString());
        if(taskNoList == null || taskNoList.isEmpty()){
            return Result.error(null);
        }
        try(Connection connection = access.getAccessConnection(BASE_PATH+"scjlaccess.mdb", "", "");){
            Result<Map<String, List<BaseProductionTask>>> result = selectByTaskNo(connection, Arrays.asList(taskNoList.split(",")));
            Map<String, List<BaseProductionTask>> result1 = result.getResult();
            log.info(result1.toString());
            return Result.ok(result1);
        }catch (Exception e){
            log.error("天造砂浆-生产任务查询 By TaskNo异常", e);
        }
        return Result.ok();
    }


    /**
     * 根据日期查询生产任务数据
     *
     * @throws Exception 异常
     *
     */
    @ApiOperation(value = "根据日期查询生产任务数据", notes = "根据日期查询生产任务数据")
    @GetMapping(value = "/productionTaskByDate")	//中间件任务
    public Result<Map<String, List<BaseProductionTask>>> productionTaskByDate(@RequestParam(name="date",required=true) String date) throws Exception {

        log.info("天造砂浆-生产任务查询 ByDate" + date);
        if(date == null || date.isEmpty()){
            return Result.error(null);
        }
        try(Connection connection = access.getAccessConnection(BASE_PATH+"scjlaccess.mdb", "", "");){
            Result<Map<String, List<BaseProductionTask>>> result = selectByDate(connection, date);
            Map<String, List<BaseProductionTask>> result1 = result.getResult();
            log.info(result1.toString());
            return Result.ok(result1);
        }catch (Exception e){
            log.error("天造砂浆-生产任务查询 ByDate", e);
        }
        return Result.ok();
    }



    /**
     * 生产任务：通过access数据库查询生产任务数据
     *
     * @param connection 连接
     * @throws Exception 异常
     *
     */
    public Result<Map<String, List<BaseProductionTask>>> select(Connection connection,Integer ids) throws Exception {
        Statement statement = connection.createStatement();
        String sql = "select * from 生产记录 where 序号 > " + ids;
        ResultSet result = statement.executeQuery(sql);
        Result<Map<String, List<BaseProductionTask>>> mapResult = getMapResult(result);
        result.close();
        statement.close();
        return mapResult;
    }



    /**
     * 生产任务：通过access数据库查询生产任务数据
     *
     * @param connection 连接
     * @throws Exception 异常
     *
     */
    public Result<Map<String, List<BaseProductionTask>>> selectByTaskNo(Connection connection, List<String> taskNoList) throws Exception {
        Statement statement = connection.createStatement();
        String sql = "select * from 生产记录 where 任务单号 IN ('" + String.join("','", taskNoList) + "')";
        ResultSet result = statement.executeQuery(sql);
        Result<Map<String, List<BaseProductionTask>>> mapResult = getMapResult(result);
        result.close();
        statement.close();
        return mapResult;
    }


    /**
     * 生产任务：通过access数据库查询生产任务数据
     *
     * @param connection 连接
     * @throws Exception 异常
     *
     */
    public Result<Map<String, List<BaseProductionTask>>> selectByDate(Connection connection, String date) throws Exception {
        Statement statement = connection.createStatement();
        String sql = "select * from 生产记录 where 时间 >= '" + date + "' AND 时间 <= '"+ date+" 23:59:59" +"'";
        ResultSet result = statement.executeQuery(sql);
        Result<Map<String, List<BaseProductionTask>>> mapResult = getMapResult(result);
        result.close();
        statement.close();
        return mapResult;
    }


    private Result<Map<String, List<BaseProductionTask>>> getMapResult(ResultSet result) throws SQLException {
        Map<String, List<BaseProductionTask>> numberMap = new HashMap<>();
        List<BaseProductionTask> list = new ArrayList<>();
        while (result.next()) {
            BaseProductionTask baseProductionTask = new BaseProductionTask();
            Integer id = result.getInt("序号");//主键id
            String orderNo = result.getString("任务单号");//任务单号
            String ratioNo = result.getString("配比编号");//配比编号
            String gl1 = result.getString("骨料1称量值");//砂1
            String gl2 = result.getString("骨料2称量值");//砂2
            String gl3 = result.getString("骨料3称量值");//砂3
            String sn1 = result.getString("水泥1称量值");//水泥1
            String sn2 = result.getString("水泥2称量值");//水泥2
            String fmh =result.getString("粉煤灰1称量值");//粉煤灰
            String tjj1 = "0";//添加剂1
            String tjj2 = result.getString("水称量值");//添加剂2
            String tjj3 = result.getString("外加剂1称量值"); //添加剂3

            String pouring = result.getString("浇注方式");//
            String customerName = result.getString("客户名称");//
            String engineeringName = result.getString("工程名称");//
            String structuralParts = result.getString("施工部位");//
            String mortar = result.getString("砼强度");//
            String slump = result.getString("塌落度");//
            String trainNum = result.getString("车次");//
            String trainVehicles = result.getString("车数");//
            String cubeNumber = result.getString("方数");//
            String vehicleQuantity = result.getString("车方量");//
            String wagon = result.getString("车号");//
            String driver = result.getString("司机");//
            String operator = result.getString("操作员");//
            String serialNumber = result.getString("流水号");//
            String contractCode = result.getString("合同编号");//
            String productionLine = result.getString("连线生产");//
            String createTime = result.getString("时间");//
            baseProductionTask.setId(id.toString());
            baseProductionTask.setSort(id);
            baseProductionTask.setOrderNo(orderNo);
            baseProductionTask.setRatioNo(ratioNo);
            baseProductionTask.setS1(gl1);
            baseProductionTask.setS2(gl2);
            baseProductionTask.setS3(gl3);
            baseProductionTask.setS4(sn1);
            baseProductionTask.setS5(sn2);
            baseProductionTask.setS6(fmh);
            baseProductionTask.setS7(tjj1);
            baseProductionTask.setS8(tjj2);
            baseProductionTask.setS9(tjj3);
            baseProductionTask.setPouring(pouring);
            baseProductionTask.setCustomerName(customerName);
            baseProductionTask.setEngineeringName(engineeringName);
            baseProductionTask.setStructuralParts(structuralParts);
            baseProductionTask.setMortar(mortar);
            baseProductionTask.setSlump(slump);
            baseProductionTask.setTrainNum(trainNum);
            baseProductionTask.setTrainVehicles(trainVehicles);
            baseProductionTask.setCubeNumber(cubeNumber);
            baseProductionTask.setVehicleQuantity(vehicleQuantity);
            baseProductionTask.setWagon(wagon);
            baseProductionTask.setDriver(driver);
            baseProductionTask.setOperator(operator);
            baseProductionTask.setSerialNumber(serialNumber);
            baseProductionTask.setContractCode(contractCode);
            baseProductionTask.setProductionLine(productionLine);
            if(createTime!= null){
                baseProductionTask.setCreateTime(DateUtil.parse(createTime, "yyyy-MM-dd"));
            }
            list.add(baseProductionTask);
        }
        numberMap.put("items", list);
        return Result.ok(numberMap);
    }




}
